﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;
using System.Collections.Concurrent;
using System.Reflection.Emit;
using System.Threading;
#pragma warning disable 1573, 1591 // xml comments
using Dapper;


using HBase;
using hCommand;
using System.Linq.Expressions;

namespace Dapper {
    public static class SqlMapperExtensions {
        public interface IProxy {
            bool IsDirty { get; set; }
        }
        private static readonly ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>> KeyProperties = new ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>> TypeProperties = new ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>> ComputedProperties = new ConcurrentDictionary<RuntimeTypeHandle ,IEnumerable<PropertyInfo>>();
        private static readonly ConcurrentDictionary<RuntimeTypeHandle ,string> GetQueries = new ConcurrentDictionary<RuntimeTypeHandle ,string>();

        private static readonly Dictionary<string ,ISqlAdapter> AdapterDictionary = new Dictionary<string ,ISqlAdapter>() {
                                                                                            {"sqlconnection", new SqlServerAdapter()},
                                                                                            {"npgsqlconnection", new PostgresAdapter()},
                                                                                            {"sqliteconnection", new SQLiteAdapter()}
                                                                                        };
        private static IEnumerable<PropertyInfo> ComputedPropertiesCache(Type type) {
            IEnumerable<PropertyInfo> pi;
            if( ComputedProperties.TryGetValue(type.TypeHandle ,out pi) ) {
                return pi;
            }
            var computedProperties = TypePropertiesCache(type).Where(p => p.GetCustomAttributes(true).Any(a => a is ComputedAttribute)).ToList();
            ComputedProperties[type.TypeHandle] = computedProperties;
            return computedProperties;
        }
        private static IEnumerable<PropertyInfo> KeyPropertiesCache(Type type) {
            IEnumerable<PropertyInfo> pi;
            if( KeyProperties.TryGetValue(type.TypeHandle ,out pi) ) {
                return pi;
            }
            var allProperties = TypePropertiesCache(type);
            var keyProperties = allProperties.Where(p => p.GetCustomAttributes(true).Any(a => a is KeyAttribute)).ToList();
            if( keyProperties.Count == 0 ) {
                var idProp = allProperties.Where(p => p.Name.ToLower() == "id").FirstOrDefault();
                if( idProp != null ) {
                    keyProperties.Add(idProp);
                }
            }
            KeyProperties[type.TypeHandle] = keyProperties;
            return keyProperties;
        }
        private static IEnumerable<PropertyInfo> TypePropertiesCache(Type type) {
            IEnumerable<PropertyInfo> pis;
            if( TypeProperties.TryGetValue(type.TypeHandle ,out pis) ) {
                return pis;
            }
            var properties = type.GetProperties().Where(IsWriteable).ToArray();
            TypeProperties[type.TypeHandle] = properties;
            return properties;
        }
        public static bool IsWriteable(PropertyInfo pi) {
            object[] attributes = pi.GetCustomAttributes(typeof(WriteAttribute) ,false);
            if( attributes.Length == 1 ) {
                WriteAttribute write = ( WriteAttribute ) attributes[0];
                return write.Write;
            }
            return true;
        }
        /// <summary>
        /// Returns a single entity by a single id from table "Ts". T must be of interface type. 
        /// Id must be marked with [Key] attribute.
        /// Created entity is tracked/intercepted for changes and used by the Update() extension. 
        /// </summary>
        /// <typeparam name="T">Interface type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <returns>Entity of T</returns>
        public static T Get<T>(this IDbConnection connection ,dynamic id ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            string sql;
            if( !GetQueries.TryGetValue(type.TypeHandle ,out sql) ) {
                var keys = KeyPropertiesCache(type);
                if( keys.Count() > 1 )
                    throw new DataException("Get<T> only supports an entity with a single [Key] property");
                if( keys.Count() == 0 )
                    throw new DataException("Get<T> only supports en entity with a [Key] property");
                var onlyKey = keys.First();
                var name = ToStr.GetTableName(type);
                // TODO: pluralizer 
                // TODO: query information schema and only select fields that are both in information schema and underlying class / interface 
                sql = "select * from " + name + " where " + onlyKey.Name + " = @id";
                GetQueries[type.TypeHandle] = sql;
            }
            var dynParms = new DynamicParameters();
            dynParms.Add("@id" ,id);
            T obj = null;
            if( type.IsInterface ) {
                throw new Exception("接口无效"+ type);
            } else {
                obj = connection.Query<T>(sql ,dynParms ,transaction: transaction ,commandTimeout: commandTimeout).FirstOrDefault();
            }
            return obj;
        }
        static Dictionary<Type ,string> idQueryDict = new Dictionary<Type ,string>();

        static String getSql(Type type) {
            lock (idQueryDict) {
                if (!idQueryDict.ContainsKey(type))
                {
                    var name = ToStr.GetTableName(type);
                    idQueryDict.Add(type, "select * from " + name + " where id = @id");
                }
            }
           
            string sql = idQueryDict[type];
            return sql;
        }
        public static T GetById<T>(this IDbConnection connection ,long id ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            String sql = getSql( type);
            var dynParms = new DynamicParameters();
            dynParms.Add("@id" ,id);
            T obj = null;
            if( type.IsInterface ) {
                var res = connection.Query(sql ,dynParms).FirstOrDefault() as IDictionary<string ,object>;
                if( res == null )
                    return ( T ) (( object ) null);
                throw new Exception(type + "接口无效" + id);
            } else {
                obj = connection.Query<T>(sql ,dynParms ,transaction: transaction ,commandTimeout: commandTimeout).FirstOrDefault();
            }
            return obj;
        }
        /// <summary>
        /// Returns a single entity by a single id from table "Ts". T must be of interface type. 
        /// Id must be marked with [Key] attribute.
        /// Created entity is tracked/intercepted for changes and used by the Update() extension. 
        /// </summary>
        /// <typeparam name="T">Interface type to create and populate</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="id">Id of the entity to get, must be marked with [Key] attribute</param>
        /// <returns>Entity of T</returns>
        /// 
        public static List<T> GetAll<T>(this IDbConnection connection ,string whereStr = null ,object param = null ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {

            return getField<T>(connection ,"*" ,whereStr ,param ,transaction ,commandTimeout);
        }
        public static List<T> getField<T>(this IDbConnection connection ,String field ,string whereStr = null ,object param = null ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            string sql = "select " + field + " from " + name + " where 1=1 " + whereStr;
            return connection.Query<T>(sql ,param ,transaction: transaction ,commandTimeout: commandTimeout)
                .ToList();
        }



        /// <summary>
        /// Inserts an entity into table "Ts" and returns identity id.
        /// </summary>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToInsert">Entity to insert</param>
        /// <returns>Identity of inserted entity</returns>
        public static long Insert<T>(this IDbConnection connection ,T entityToInsert ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = entityToInsert.GetType();
            setId(entityToInsert,type, connection, transaction);
            var name = ToStr.GetTableName(type);
            var sbColumnList = new StringBuilder(null);
            var allProperties = TypePropertiesCache(type);
            var keyProperties = KeyPropertiesCache(type);
            var computedProperties = ComputedPropertiesCache(type);
            #region 自增主键注释
            //排除主键
            //var allPropertiesExceptKeyAndComputed = allProperties.Except(keyProperties.Union(computedProperties));
            //for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
            //{
            //    var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
            //    sbColumnList.AppendFormat("[{0}]", camelCase2_(property.Name));
            //    if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
            //        sbColumnList.Append(", ");
            //}
            //var sbParameterList = new StringBuilder(null);
            //for (var i = 0; i < allPropertiesExceptKeyAndComputed.Count(); i++)
            //{
            //    var property = allPropertiesExceptKeyAndComputed.ElementAt(i);
            //    sbParameterList.AppendFormat("@{0}", property.Name);
            //    if (i < allPropertiesExceptKeyAndComputed.Count() - 1)
            //        sbParameterList.Append(", ");
            //}
            #endregion
            for( var i = 0;i < allProperties.Count();i++ ) {
                var property = allProperties.ElementAt(i);
                sbColumnList.AppendFormat("[{0}]" ,ToStr.camelCase2_(property.Name));
                if( i < allProperties.Count() - 1 )
                    sbColumnList.Append(", ");
            }
            var sbParameterList = new StringBuilder(null);
            for( var i = 0;i < allProperties.Count();i++ ) {
                var property = allProperties.ElementAt(i);
                sbParameterList.AppendFormat("@{0}" ,property.Name);
                if( i < allProperties.Count() - 1 )
                    sbParameterList.Append(", ");
            }
            ISqlAdapter adapter = GetFormatter(connection);
            int id = adapter.Insert(connection ,transaction ,commandTimeout ,name ,sbColumnList.ToString() ,sbParameterList.ToString() ,keyProperties ,entityToInsert);
            return id;
        }

       static void setId<T>( T e,Type type, IDbConnection connection , IDbTransaction transaction ) {
            
            PropertyInfo p = type.GetProperty("id");
            if (p != null) {
                object v = p.GetValue(e);
                if ((long)v == 0) {
                    p.SetValue(e,getId(connection, transaction));
                }
            }
            PropertyInfo pt = type.GetProperty("createTime");
            if (pt != null) {
                DateTime dt=(DateTime) pt.GetValue(e);
                if (dt.Ticks < 10000) {

                    DateTime dtn ;
                    if (BaseConfig.prefixal == GKDBType.mySql) {
                        dtn= connection.Query<DateTime>("select now()",null, transaction).Single();
                    } else {
                        dtn =connection.Query<DateTime>("select getdate()", null, transaction).Single();
                    }

                    pt.SetValue(e, dtn);
                }
           
                
            }
        }



 


 

        static long SEQUENCES_ID = 0;
        static int index = 0;
        static object obj=new object(); 
        private static long getId(IDbConnection connection, IDbTransaction transaction) {
            lock (obj) {
                long id = 0;
                if (SEQUENCES_ID == 0 || index == 99) { 
                    if (BaseConfig.prefixal == GKDBType.sqlserver) {
                        SEQUENCES_ID= connection.Query<long>("SELECT NEXT VALUE FOR sequence_id", null, transaction).Single();
                    } else if (BaseConfig.prefixal == GKDBType.mySql) {
                        SEQUENCES_ID = connection.Query<long>("SELECT NEXTVAL()", null, transaction).Single();
                    } else if (BaseConfig.prefixal == GKDBType.oracle) {
                        SEQUENCES_ID = connection.Query<long>("SELECT  SEQUENCES_ID.NEXTVAL FROM dual", null, transaction).Single();
                    } else {
                        SEQUENCES_ID = 0;
                    } 
                    index = 0;
                }
                index++;
                id = SEQUENCES_ID * 100 + index;
                return id;
            }
        }





        /// <summary>
        /// Inserts an entity into table "Ts" and returns identity id.
        /// </summary>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToInsert">Entity to insert</param>
        /// <returns>Identity of inserted entity</returns>
        public static long InsertNoNull<T>(this IDbConnection connection ,T entityToInsert ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            setId(entityToInsert,type, connection,   transaction);
            var name = ToStr.GetTableName(type);
            var sbColumnList = new StringBuilder(null);
            IEnumerable<PropertyInfo> allProperties = TypePropertiesCache(type);
            IEnumerable<PropertyInfo> keyProperties = KeyPropertiesCache(type);
            List<PropertyInfo> notNullProperties = new List<PropertyInfo>();
            for( var i = 0;i < allProperties.Count();i++ ) {
                PropertyInfo property = allProperties.ElementAt(i);
                var obj = property.GetValue(entityToInsert ,null);
                if( obj == null ) {
                    continue;
                }
                if( property.PropertyType == typeof(DateTime) ) {
                    if( (( DateTime ) obj).Ticks == DateTime.MinValue.Ticks ) {
                        continue;
                    }
                }
                notNullProperties.Add(property);
                if( notNullProperties.Count() != 1 ) {
                    sbColumnList.Append(", ");
                }
                sbColumnList.AppendFormat("[{0}]" ,ToStr.camelCase2_(property.Name));
            }
            var sbParameterList = new StringBuilder(null);
            for( var i = 0;i < notNullProperties.Count();i++ ) {
                var property = notNullProperties.ElementAt(i);
                sbParameterList.AppendFormat("@{0}" ,property.Name);
                if( i < notNullProperties.Count() - 1 )
                    sbParameterList.Append(", ");
            }
            ISqlAdapter adapter = GetFormatter(connection);
            int id = adapter.Insert(connection ,transaction ,commandTimeout ,name ,sbColumnList.ToString() ,sbParameterList.ToString() ,keyProperties ,entityToInsert);
            return id;
        }


        
        public static bool Update<T>(this IDbConnection connection, T entityToUpdate, Expression<Func<T, object>> properties=null, IDbTransaction transaction = null) where T : class
        {
            String sql = null;
           
            if (properties == null) {
                var proxy = entityToUpdate as IProxy;
                if (proxy != null && !proxy.IsDirty)
                {
                    return false;
                }
                sql = getUpdateAllSql(entityToUpdate);
                
            }else {
                String[] filedNames = null;
                if (properties.Body is NewExpression) {
                    filedNames = ((NewExpression)properties.Body).Members.Select(x => x.Name).ToArray();
                }else  if (properties.Body is MemberExpression) {
                    filedNames = new string[] { ((MemberExpression)properties.Body).Member.Name };
                }  else {
                    throw new Exception("属性未配置");
                } 
               sql = updataSql(entityToUpdate, filedNames);
                 
            }
            var c = connection.Execute(sql, entityToUpdate, transaction);
            return c > 0;

        }


     static  String updataSql<T>(T entityToUpdate, String[] fields) where T : class
        {
            var type = entityToUpdate.GetType();
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set ", name);

            for (var i = 0; i < fields.Count(); i++)
            {
                var property = fields[i];
                sb.AppendFormat("{0} = @{1}  ", ToStr.camelCase2_(property), property);
                if(i != fields.Count()-1){ 
                       sb.AppendFormat(" , ");
                }
              
            }
            sb.AppendFormat("  where ");

            var keyProperties = KeyPropertiesCache(type);
            for (var i = 0; i < keyProperties.Count(); i++)
            {
                var n = keyProperties.ElementAt(i).Name;
                sb.AppendFormat("{0} = @{1}", ToStr.camelCase2_(n), n);
                if (i < keyProperties.Count() - 1) {
                    sb.AppendFormat(" and ");
                }  
            }
            return sb.ToString();

        }




        static String getUpdateAllSql<T>(T entityToUpdate) where T : class
        {
            var type = entityToUpdate.GetType();
            var keyProperties = KeyPropertiesCache(type);
            if (!keyProperties.Any())
                throw new ArgumentException("Entity must have at least one [Key] property");
            var name = ToStr.GetTableName(type);

            List<String> fields = new List<String>();
         
            var allProperties = TypePropertiesCache(type);
            var computedProperties = ComputedPropertiesCache(type);
            var nonIdProps = allProperties.Except(keyProperties.Union(computedProperties));
            for (var i = 0; i < nonIdProps.Count(); i++)
            {
                var property = nonIdProps.ElementAt(i);
                if (property.Name.ToLower() == "id")
                {
                    continue;
                }
                var obj = property.GetValue(entityToUpdate, null);
                if (property.PropertyType == typeof(DateTime))
                {
                    if (((DateTime)obj).Ticks == DateTime.MinValue.Ticks)
                    {
                        continue;
                    }
                } 
                fields.Add(property.Name);  
            } 
            return updataSql<T>(entityToUpdate, fields.ToArray());

        }
       
        public static bool updateNotNull<T>(this IDbConnection connection ,T entityToUpdate ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var proxy = entityToUpdate as IProxy;
            if( proxy != null ) {
                if( !proxy.IsDirty )
                    return false;
            }
            var type = entityToUpdate.GetType();
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("update {0} set " ,name);
            var allProperties = TypePropertiesCache(type);
            for( var i = 0;i < allProperties.Count();i++ ) {
                var property = allProperties.ElementAt(i);
                if( property.Name.ToLower() == "id" ) {
                    continue;
                }
                var obj = property.GetValue(entityToUpdate ,null);
                if( obj == null ) {
                    continue;
                }
                if( property.PropertyType == typeof(DateTime) ) {
                    if( (( DateTime ) obj).Ticks == DateTime.MinValue.Ticks ) {
                        continue;
                    }
                }
                if( property.PropertyType == typeof(int) || property.PropertyType == typeof(long) ) {
                    if( Convert.ToInt32(obj) == 0 ) {
                        continue;
                    }
                }
                sb.AppendFormat("{0} = @{1} , " ,ToStr.camelCase2_(property.Name) ,property.Name);
            }
            sb.Append(" id=id where id=@id");
            var updated = connection.Execute(sb.ToString() ,entityToUpdate ,commandTimeout: commandTimeout ,transaction: transaction);
            return updated > 0;
        }
 
        /// <summary>
        /// Delete entity in table "Ts".
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <param name="entityToDelete">Entity to delete</param>
        /// <returns>true if deleted, false if not found</returns>
        public static bool Delete<T>(this IDbConnection connection ,T entityToDelete ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            if( entityToDelete == null )
                throw new ArgumentException("Cannot Delete null Object" ,"entityToDelete");
            var type = typeof(T);
            var keyProperties = KeyPropertiesCache(type);
            if( keyProperties.Count() == 0 )
                throw new ArgumentException("Entity must have at least one [Key] property");
            var name = ToStr.GetTableName(type);
            var sb = new StringBuilder();
            sb.AppendFormat("delete from {0} where " ,name);
            for( var i = 0;i < keyProperties.Count();i++ ) {
                var property = keyProperties.ElementAt(i);
                sb.AppendFormat("{0} = @{1}" ,property.Name ,property.Name);
                if( i < keyProperties.Count() - 1 )
                    sb.AppendFormat(" and ");
            }
            var deleted = connection.Execute(sb.ToString() ,entityToDelete ,transaction: transaction ,commandTimeout: commandTimeout);
            return deleted > 0;
        }
        public static int BatchInsert<T>(this IDbConnection connection ,List<T> entities ,IDbTransaction transaction = null) {
           
            var type = typeof(T);
            var tbname = ToStr.GetTableName(type);
           
            if ( entities != null && entities.Count > 0 ) {
               
                var colms = new List<string>();
              
                var ps = entities.First().GetType().GetProperties().Where(IsWriteable).ToList();
                foreach( var p in ps ) {
                    colms.Add(ToStr.camelCase2_(p.Name));
                }
                var paramList = new DynamicParameters();
                var paramsNames = new List<string>();
                var s = 0;
                var n = "a";
                foreach( var item in entities ) {
                    setId(item, type, connection, transaction);
                    var toNames = new List<string>();
                    foreach( var p in ps ) {
                        var pname = $"@{n}{s}";
                        var pvalue = p.GetValue(item ,null);
                        if( pvalue != null && pvalue is DateTime ) {
                            if( (( DateTime ) pvalue).Year == 1 ) {
                                pvalue = null;
                            }
                        }
                        toNames.Add(pname);
                        paramList.Add(pname ,pvalue);
                        s++;
                    }
                    paramsNames.Add($"({string.Join("," ,toNames)})");
                }
                var sql = $"INSERT INTO {tbname} ({string.Join("," ,colms)}) VALUES {string.Join("," ,paramsNames)}";
                return connection.Execute(sql ,paramList ,transaction);
            }
            throw new Exception(tbname + "传入参数先");
        }

        public static int BatchUpdate<T>(this IDbConnection connection ,List<T> entities ,string tbname ,params String[] propertys) {
            if( entities == null || entities.Count == 0 ) {
                return 0;
            }
            List<PropertyInfo> psAll = entities.First().GetType().GetProperties().ToList();
            List<PropertyInfo> ps = new List<PropertyInfo>();
            var colms = new List<string>();
            PropertyInfo idInfo = null;
            foreach( var p in psAll ) {
                if( propertys.Contains(p.Name) ) {
                    ps.Add(p);
                    colms.Add(ToStr.camelCase2_(p.Name));
                }

                if( p.Name == "id" ) {
                    idInfo = p;
                }
            }
            if( idInfo == null ) {
                throw new Exception("无id不能更新");
            }

            var paramList = new DynamicParameters();
            var s = 0;
            var sqls = new List<string>();
            foreach( var entity in entities ) {
                var sets = new List<string>();
                for( int i = 0;i < ps.Count;i++ ) {
                    var p = ps[i];
                    var pname = $"@a{s}";
                    var pvalue = p.GetValue(entity ,null);
                    paramList.Add(pname ,pvalue);
                    sets.Add($"{colms[i]}={pname}");
                    s++;
                }
                var idValue = idInfo.GetValue(entity ,null);
                paramList.Add($"id{s}" ,idValue);
                sqls.Add($"update {tbname} set {string.Join("," ,sets)} where id=@id{s}");
                s++;
            }

            return connection.Execute(string.Join(";" ,sqls) ,paramList);


        }
        /// <summary>
        /// Delete all entities in the table related to the type T.
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <returns>true if deleted, false if none found</returns>
        public static bool Delete<T>(this IDbConnection connection ,string whereStr ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0} where 1=1 and {1} " ,name ,whereStr);
            var deleted = connection.Execute(statement ,null ,transaction: transaction ,commandTimeout: commandTimeout);
            return deleted > 0;
        }

        public static bool Delete<T>(this IDbConnection connection ,long id ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            return delete(connection ,type ,id ,transaction ,commandTimeout);
        }
        public static bool delete(this IDbConnection connection ,BaseEntity e ,IDbTransaction transaction = null ,int? commandTimeout = null) {
            Type type = e.GetType();
            return delete(connection ,type ,e.id ,transaction ,commandTimeout);
        }
        public static bool delete(this IDbConnection connection ,Type type ,long id ,IDbTransaction transaction = null ,int? commandTimeout = null) {
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0} where id={1} " ,name ,id);
            var deleted = connection.Execute(statement ,null ,transaction: transaction ,commandTimeout: commandTimeout);
            return deleted > 0;
        }

        public static int RunProcedure(this IDbConnection connection ,String proc ,DynamicParameters param, IDbTransaction trans = null) {
            return connection.Execute(proc ,param , trans, null ,CommandType.StoredProcedure);
        }
        /// <summary>
        /// Delete all entities in the table related to the type T.
        /// </summary>
        /// <typeparam name="T">Type of entity</typeparam>
        /// <param name="connection">Open SqlConnection</param>
        /// <returns>true if deleted, false if none found</returns>
        public static bool DeleteAll<T>(this IDbConnection connection ,IDbTransaction transaction = null ,int? commandTimeout = null) where T : class {
            var type = typeof(T);
            var name = ToStr.GetTableName(type);
            var statement = String.Format("delete from {0}" ,name);
            var deleted = connection.Execute(statement ,null ,transaction: transaction ,commandTimeout: commandTimeout);
            return deleted > 0;
        }
        public static ISqlAdapter GetFormatter(IDbConnection connection) {
            string name = connection.GetType().Name.ToLower();
            if( !AdapterDictionary.ContainsKey(name) )
                return new SqlServerAdapter();
            return AdapterDictionary[name];
        }
      
    }
   
}
public interface ISqlAdapter {
    int Insert(IDbConnection connection ,IDbTransaction transaction ,int? commandTimeout ,String tableName ,string columnList ,string parameterList ,IEnumerable<PropertyInfo> keyProperties ,object entityToInsert);
}
public class SqlServerAdapter :ISqlAdapter {
    public int Insert(IDbConnection connection ,IDbTransaction transaction ,int? commandTimeout ,String tableName ,string columnList ,string parameterList ,IEnumerable<PropertyInfo> keyProperties ,object entityToInsert) {
        string cmd = String.Format("insert into {0} ({1}) values ({2}) " ,tableName ,columnList ,parameterList);
        int id = connection.ExecuteScalar<int>(cmd ,entityToInsert ,transaction: transaction ,commandTimeout: commandTimeout);


        //NOTE: would prefer to use IDENT_CURRENT('tablename') or IDENT_SCOPE but these are not available on SQLCE
        //var r = connection.Query("select @@IDENTITY id", transaction: transaction, commandTimeout: commandTimeout);
        //int id = (int)r.First().id;
        //if (keyProperties.Any())
        //	keyProperties.First().SetValue(entityToInsert, id, null);
        return id;
    }
}
public class PostgresAdapter :ISqlAdapter {
    public int Insert(IDbConnection connection ,IDbTransaction transaction ,int? commandTimeout ,String tableName ,string columnList ,string parameterList ,IEnumerable<PropertyInfo> keyProperties ,object entityToInsert) {
        StringBuilder sb = new StringBuilder();
        sb.AppendFormat("insert into {0} ({1}) values ({2})" ,tableName ,columnList ,parameterList);
        // If no primary key then safe to assume a join table with not too much data to return
        if( !keyProperties.Any() )
            sb.Append(" RETURNING *");
        else {
            sb.Append(" RETURNING ");
            bool first = true;
            foreach( var property in keyProperties ) {
                if( !first )
                    sb.Append(", ");
                first = false;
                sb.Append(property.Name);
            }
        }
        var results = connection.Query(sb.ToString() ,entityToInsert ,transaction: transaction ,commandTimeout: commandTimeout);
        // Return the key by assinging the corresponding property in the object - by product is that it supports compound primary keys
        int id = 0;
        foreach( var p in keyProperties ) {
            var value = (( IDictionary<string ,object> ) results.First())[p.Name.ToLower()];
            p.SetValue(entityToInsert ,value ,null);
            if( id == 0 )
                id = Convert.ToInt32(value);
        }
        return id;
    }
}
public class SQLiteAdapter :ISqlAdapter {
    public int Insert(IDbConnection connection ,IDbTransaction transaction ,int? commandTimeout ,String tableName ,string columnList ,string parameterList ,IEnumerable<PropertyInfo> keyProperties ,object entityToInsert) {
        string cmd = String.Format("insert into {0} ({1}) values ({2})" ,tableName ,columnList ,parameterList);
        connection.Execute(cmd ,entityToInsert ,transaction: transaction ,commandTimeout: commandTimeout);
        var r = connection.Query("select last_insert_rowid() id" ,transaction: transaction ,commandTimeout: commandTimeout);
        int id = ( int ) r.First().id;
        if( keyProperties.Any() )
            keyProperties.First().SetValue(entityToInsert ,id ,null);
        return id;
    }
}
